﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using fuzhu;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Net;
using System.IO;

namespace GLY_XieTong
{

    public class TongBu
    {
        
        string cConStr { get;set; }
        // A,B 账连接字符串,
        /// <summary>
        /// 来源链接字符串
        /// </summary>
        public string cConStrA { get; set; }
        /// <summary>
        /// 目标字符串
        /// </summary>
        public string cConStrB { get; set; }
        public string cConStrZD { get; set; }
        public string cError { get; set; }

        public int iInterval { get; set; }
        public int iDBID { get; set; }

        public TongBu(string conStr,int DBID)
         {
            iDBID = DBID;
            cConStr = conStr;
            string sql = string.Format("select CSOURCE,CTARGET from zdy_inter_database WHERE DBID = {0}", DBID);
            DataTable dataTable = DbHelper2.ExecuteTable(sql, conStr);
            if (dataTable.Rows.Count > 0)
            {
                cConStrA = DbHelper.GetDbString(dataTable.Rows[0]["CSOURCE"]);
                cConStrB = DbHelper.GetDbString(dataTable.Rows[0]["CTARGET"]);
                cError = "";
            }
            else
            {
                cConStrA = "";
                cConStrB = "";
                cError = "同步账套出错！";

            }
         }

        #region 同步U8单据
        public string InsAll()
        {

            cError = "";
            bool bCheck = checkYiJia();
            try
            {
                string sql = string.Format(@"SELECT A.DBID,a.CLX,c.id,c.cTBLeiXing
  FROM [ZDY_INTER_XIETONG] a
  INNER JOIN dbo.zdy_inter_database b ON a.DBID=b.DBID
  INNER JOIN zdy_inter_table  c ON a.CLX=c.clx
  WHERE a.DBID={0} and  b.BUSE = 1 ", iDBID);
                DataTable dt = DbHelper2.ExecuteTable(sql, cConStr);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string cLxmx = DbHelper.GetDbString(dt.Rows[i]["CLX"]);
                        int iId = DbHelper.GetDbInt(dt.Rows[i]["id"]);
                        string cTBLeixing = DbHelper.GetDbString(dt.Rows[i]["cTBLeiXing"]);
                        if (cLxmx != "刷卡记录" && cLxmx != "刷卡记录A")
                        {
                           //1： 档案， 2：单据  3： MES带identity的单据，9： 转换的单据，
                           //11： 只增加Add
                                if (cTBLeixing=="1" && !bCheck)
                                {
                                    cError += cLxmx + "同步结果:" + INSDangAn(cLxmx) + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"\r\n";
                                }
                                else if (cTBLeixing == "2"  && !bCheck)
                                {
                                    cError += cLxmx + "同步结果:" + INSVouch(cLxmx, "") + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                                }
                            else if (cTBLeixing == "4" && !bCheck)
                            {
                                cError += cLxmx + "同步结果:" + INSVouch(cLxmx, "") + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                            }
                            else if (cTBLeixing == "9" && !bCheck)
                                {
                                //转换的单据
                                    cError += cLxmx + "同步结果:" + INSVouchZhuan(cLxmx, "") + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                                }
                                else if (cTBLeixing == "3")
                                {
                                //用MES的插入，因为有标识列
                                    cError += cLxmx + "同步结果:" + INSVouchMES(cLxmx, "") + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                                }
                                else if (cTBLeixing == "11" && !bCheck)
                                {
                                    //只新增不删除
                                    cError += cLxmx + "同步结果:" + INSVouchAdd(cLxmx, "") + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                                }
                        }
                    }
                }
                else
                {
                    cError = "数据库同步号错误";
                }
                return cError;

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        #endregion

        public string INSModel(string cLx,string cFilterOri)
        {
            string sql = string.Format(@" SELECT  [id] ,[clx],cTBLeiXing
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
            string cRe = "";
            DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
            if (dt1.Rows.Count == 0)
            {
                cRe = "没有配置此同步任务！";
            }
            else
            {
                int iId = DbHelper.GetDbInt(dt1.Rows[0]["id"]);
                string cTBLeixing = DbHelper.GetDbString(dt1.Rows[0]["cTBLeiXing"]);
                if (cTBLeixing == "1" )
                {
                    cRe = INSDangAn(cLx)  ;
                }
                else if (cTBLeixing == "2"  )
                {
                    cRe = INSVouch(cLx, cFilterOri);
                }
                else if (cTBLeixing == "4")
                {
                    cRe = INSVouch(cLx, cFilterOri);
                }
                else if (cTBLeixing == "9"  )
                {
                    //转换的单据
                    cRe = INSVouchZhuan(cLx, cFilterOri);
                }
                else if (cTBLeixing == "3")
                {
                    //用MES的插入，因为有标识列
                    cRe = INSVouchMES(cLx, cFilterOri);
                }
                else if (cTBLeixing == "11"  )
                {
                    //只新增不删除
                    cRe = INSVouchAdd(cLx, cFilterOri);
                }
                //if ((iId >= 100 && iId < 150) || (iId >= 450 && iId < 900))
                //{
                //    cRe = INSVouch(cLx, cFilterOri);
                //}
                //else if (iId >= 150 && iId < 200)
                //{
                //    cRe = INSVouch666(cLx, cFilterOri);
                //}
                //else if (iId < 100 || (iId >= 400 && iId < 450))
                //{
                //    cRe = INSDangAn(cLx);
                //}
                //else if (iId > 200 && iId < 300)
                //{
                //    cRe = INSVouchZhuan(cLx, cFilterOri);
                //}
                //else if (iId > 300 && iId < 400)
                //{
                //    cRe = INSVouchMES(cLx, cFilterOri);

                //}
                //else if (iId > 900)
                //{
                //    cRe = INSVouchAdd(cLx, cFilterOri);
                //}


            }
            return cRe;
        }

        #region  插入基础档案
        /// <summary>
        /// 基础档案,不考虑时间因素，考虑双主键和过滤条件
        /// </summary>
        public string INSDangAn(string cLx)
        {
            try
            {
                //CheckTimeTable();
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[PK],PK2,CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable =  DbHelper.GetDbString(dt1.Rows[0]["ctable"]);              
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //第二主键
                    string cPK2 = DbHelper.GetDbString(dt1.Rows[0]["PK2"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    
                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                      
   //                 sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   //FOR XML PATH('')),1,1,'')", cLx, cTarTable);
   //                 string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
   //                 //查询的字段，如果时间戳不为空，增加时间戳
   //                 string cSelectColumn = cInsertColumn;

                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                      
                    }
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);                   
                    string cRelation = "";
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    { 
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                    }

                    #region 权限更改成动态，使用|区分，写在一个字段里
                    //if (cLx == "权限")
                    //{
                    //    //权限是5个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cAcc_Id=T.cAcc_Id and  Tmp.iYear=T.iYear and  Tmp.cUser_Id=T.cUser_Id and  Tmp.cAuth_Id=T.cAuth_Id and  Tmp.iIsUser=T.iIsUser");
                    //}
                    //else if (cLx == "字段数据权限")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cUserId=T.cUserId and  Tmp.cKey=T.cKey and  Tmp.cFld=T.cFld  ");
                    //}
                    //else if (cLx == "业务对象表")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cBusObId=T.cBusObId and  Tmp.iAuthType=T.iAuthType and  Tmp.langid=T.langid  ");
                    //}
                    //else if (cLx == "字段权限分配")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cUserId=T.cUserId and  Tmp.cBusObId=T.cBusObId and  Tmp.cACCode=T.cACCode  ");
                    //}
                    //else if (cLx == "客户管理维度权限")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.Account_ID=T.Account_ID and  Tmp.Privilege_Type=T.Privilege_Type and  Tmp.Privilege_ID=T.Privilege_ID  ");
                    //}
                    //else if (!string.IsNullOrEmpty(cPK2))
                    //{
                    //    cRelation = string.Format(" and  Tmp.{0}=T.{0} and  Tmp.{1}=T.{1}", cPK, cPK2);
                    //}
                    //else
                    //{
                    //    cRelation = string.Format(" and  Tmp.{0}=T.{0}  ", cPK);
                    //}
                    #endregion
                    //时间字段 及过滤条件, 多时间段也使用 | 进行分割
                    string cDateCol  = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);
                 
                    if (!string.IsNullOrEmpty(cDateCol))
                    {
                        sql = string.Format("SELECT DUPDATETIME  FROM ZDY_INTER_XIETONG WHERE dbid ={0} AND clx = '{1}'  ", iDBID, cLx);
                        DateTime dDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                         string[] cDateArray = cDateCol.Split('|');
                        foreach (string i in cDateArray)
                        {
                            //权限是5个主键，程序里直接定义
                            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dDate);
                        }
                       
                    }
                    //对账套的过滤设置
                    sql = string.Format("SELECT  DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid ={0} AND clx = '{1}'  ", iDBID, cLx);
                    string cDBFilter = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cDBFilter) && cDBFilter!="")
                    {
                        cFilter += cDBFilter;
                    }
                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    
                    //创建目标库临时表
                    var tempTableName = @"#Temp" + cTarTable;
                    var createtempsql = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableName, cTarTable);


                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable , cInsertColumn, tempTableName, cRelation);

                   
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableName, cRelation);

                    var deletesql = "";
                    if (cLx == "操作员")
                    {
                        deletesql = string.Format(" delete From a from {0} T,ua_log a   where T.cuser_id  =a.cuser_id and  not exists(select 1 from {1} Tmp where  1=1 {2}) {3}  ", cTarTable, tempTableName, cRelation, cDelFilter);
                    }

                    deletesql += string.Format(" delete From T from {0} T  where not exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable,tempTableName, cRelation,cDelFilter);

                    var dropsql = string.Format("DROP TABLE {0};", tempTableName);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                   

                    //获取源数据库的内容
                    var selectsql = string.Format("select {0} from {1} where 1=1 {2}", cSelectColumn, cTarTable,cFilter);
                    DataTable dt = DbHelper2.ExecuteTable(selectsql, cConStrA);

                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = createtempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableName;
                                    bulkcopy.WriteToServer(dt);
                                    bulkcopy.Close();
                                }
                                //执行更新,有更新栏目的才进行更新
                                command.CommandTimeout = 300;
                                int updateQuery = 0;
                                if (!string.IsNullOrEmpty(cUpdateColunm))
                                {
                                    command.CommandText = updatesql;
                                    updateQuery = command.ExecuteNonQuery();
                                }
                               
                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery =command.ExecuteNonQuery();
                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();
                                //时间戳表的更新

                               
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    string cStampRelation = "";
                                    //不足5位主键的补足5位，后面的补空‘’
                                    string[] newPKAarray = new string[5];
                                    for (int i = 0; i <5; i++)
                                    {
                                        if (i< cPKArray.Length)
                                        {
                                            newPKAarray[i] = cPKArray[i];
                                            if (i == 0)
                                            {
                                                cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                            }
                                            else
                                            {
                                                cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}",i+1, cPKArray[i]);
                                            }
                                        }
                                        else
                                        {
                                            newPKAarray[i] = "0";
                                        }
                                    }

                                    //设置新的关联关系
                                  
                                   

                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                    select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                        cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableName, cStampRelation);
                                        command.ExecuteNonQuery();
                                        //相同的更新
                                        command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cStampRelation);
                                        command.ExecuteNonQuery();

                                    #region  根据主键插入，作废，更改成上面动态的
                                    //                                   {
                                    //                                       //主键没有的插入
                                    //                                       command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,ctimestamp)
                                    //                                   select '{0}',{1},{4},{2}  from {3} Tmp where not exists (select 1 from zdy_zd_stamp T where t.cpk=Tmp.{1} and clx='{0}') ",
                                    //                                       cLx, cPK, cTimestamp, tempTableName, cPK2);
                                    //                                       command.ExecuteNonQuery();
                                    //                                       //相同的更新
                                    //                                       command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0},ddate= getdate()
                                    //from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} and t.cpk2=Tmp.{4} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cPK, cPK2);
                                    //                                       command.ExecuteNonQuery();

                                    //
                                    #endregion}
                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                               cError = string.Format("error:{0}",  ex.Message );
                                
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError= "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql,param,cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:"+ cError;
                }
            }
            catch (Exception ex)
            {
              string   sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx,iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region  插入业务单据
        /// <summary>
        ///业务单据同步
        /// </summary>
        public string INSVouch(string cLx,string cFilterOri)
        {
            try
            {
                //CheckTimeTable();
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[ctables],[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cfrom,cTBLeiXing
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTables = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);

                    string cTBLeiXing = DbHelper.GetDbString(dt1.Rows[0]["cTBLeiXing"]);
                    //取目标表数据的过滤
                    string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //取日期进行查询条件组合
                    //DateTime  dBeginDate  ;
                    sql = string.Format("  SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);
                                     
                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                        #region 不使用过滤条件
                        //dBeginDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        ////时间字段 及过滤条件
                        //string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);
                        ////查询条件不为空，按传入的查询条件进行，如果没有按日期进行同步
                        //if (!string.IsNullOrEmpty(cFilterOri))
                        //{

                        //    cFilter += cFilterOri;
                        //}
                        //else
                        //{
                        //    if (!string.IsNullOrEmpty(cDateCol))
                        //    {
                        //        string[] cDateArray = cDateCol.Split('|');
                        //        foreach (string i in cDateArray)
                        //        {
                        //            //权限是5个主键，程序里直接定义
                        //            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dBeginDate);
                        //        }
                        //    }
                        //}
                        #endregion
                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                    //主键的处理
                    string cRelation = "";
                    string cPKS = "";  //主键的组合
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                        cPKS += string.Format("Tmp.{0},", i);
                    }
                    cPKS = cPKS.Substring(0, cPKS.Length - 1);
                    //时间戳的处理
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cStampRelation = "";
                    string[] newPKAarray = new string[5];
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //不足5位主键的补足5位，后面的补空‘’
                        for (int i = 0; i < 5; i++)
                        {
                            if (i < cPKArray.Length)
                            {
                                newPKAarray[i] = cPKArray[i];
                                if (i == 0)
                                {
                                    cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                }
                                else
                                {
                                    cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                }
                            }
                            else
                            {
                                newPKAarray[i] = "0";
                            }
                        }
                    }

                    
                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //从A数据中 取数
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    //从A系统读取数据的字段
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //查询的字段，如果时间戳不为空，增加时间戳
                    if (!string.IsNullOrEmpty(cTimestamp))
                        {
                            cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                        }

                    

                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);
                 
                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //取目标表的id
                    //创建源库临时表，存放目标表的id
                    var srcTempTable = @"#TempSrc" + cTarTable;
                    var srcCreatetempsql = string.Format("select {0},T.ctimestamp into {1} from {2} Tmp left join zdy_zd_stamp T ON T.clx='{3}' where 1=2  ", cPKS, srcTempTable, cTarTable,cLx);
                    //获取取目标表的id
                    var srcselectsql = string.Format(@"select {0},T.ctimestamp from {1} Tmp left join zdy_zd_stamp T ON T.clx='{2}' {3}   where 1=1 {4} {5}",
                        cPKS, cTarTable,cLx, cStampRelation, cFilter,cFilterSrc);
                    DataTable srcdt = DbHelper2.ExecuteTable(srcselectsql, cConStrB);
                   //存储
                    DataTable dtIns = new DataTable();
                    DataTable dtDel = new DataTable();
                    DataTable dtUps = new DataTable();
                    //如果有子表需要更新，写子表的 update
                    string cSelectColumnZB = "", cInsertColumnZB ="",  cUpdateColunmZB = "";
                    DataTable dtUpsZB = new DataTable();
                    string cPKSZB = "";
                    string cTableZB = "";
                    string cRelationZB ="";
                    if (!string.IsNullOrEmpty(cTarTables))
                    {
                        sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[ctables],[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cTarTables);
                        DataTable dt2 = DbHelper2.Execute(sql, cConStr).Tables[0];
                        if (dt2.Rows.Count > 0)
                        {
                            //只有一个主键，不再用数组分割了
                            cPKSZB = DbHelper.GetDbString(dt2.Rows[0]["PK"]);
                            cTableZB = DbHelper.GetDbString(dt2.Rows[0]["ctable"]);

                            //子表，只根据类型查询即可
                            sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'  and budaoru=0
   FOR XML PATH('')),1,1,'')", cTarTables);
                            cSelectColumnZB = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                            //子表，只根据类型查询即可
                            sql = string.Format(@"  SELECT STUFF((select ','+cname from zdy_inter_tables WHERE clx = '{0}'  and budaoru=0
   FOR XML PATH('')),1,1,'')", cTarTables);
                            cInsertColumnZB = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));


                            //更新的字段
                            sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cTarTables);
                            cUpdateColunmZB = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                            cRelationZB = string.Format(" and  Tmp.{0}=T.{0}  ", cPKSZB);
                        }
                    }
                 
                    //查询源表和目标表的差异，insert表， delete 表和更新表

                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = srcCreatetempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = srcTempTable;
                                    bulkcopy.WriteToServer(srcdt);
                                    bulkcopy.Close();
                                }

                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  " +
                        "where not exists(select 1 from {2} Tmp where 1=1 {3}) {4}", cSelectColumn, cTarTable,srcTempTable,cRelation, cFilter);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];

                                var srcDelectsql = string.Format("select {0} from  {1} Tmp  " +
                        "where not exists(select 1 from {2} T where 1=1 {3}) ", cPKS, srcTempTable,cTarTable, cRelation);
                                command.CommandText = srcDelectsql;
                                adp = new SqlDataAdapter(command);
                                ds = new DataSet();
                                adp.Fill(ds);
                                dtDel = ds.Tables[0];

                                //单据更新状态下：有时间戳，才进行更新。
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    var srcUpdatesql = string.Format("select {0} from  {1} T  " +
                        "where exists(select 1 from {2} Tmp where CONVERT(bigint,T.{4})<>Tmp.ctimestamp {3} ) ", cSelectColumn, cTarTable, srcTempTable, cRelation, cTimestamp);
                                    command.CommandText = srcUpdatesql;
                                    adp = new SqlDataAdapter(command);
                                    ds = new DataSet();
                                    adp.Fill(ds);
                                    dtUps = ds.Tables[0];

                                    if (!string.IsNullOrEmpty(cTarTables))
                                    {
                                        srcUpdatesql = string.Format("select {5} from  {6} Tmp  " +
                          "where {0} IN  (select {0} from  {1} T  " +
                            "where exists(select 1 from {2} Tmp where convert(bigint,T.{4})<>Tmp.ctimestamp {3} )) ", cPK, cTarTable, srcTempTable, cRelation, cTimestamp,
                            cSelectColumnZB, cTableZB);
                                        command.CommandText = srcUpdatesql;
                                        adp = new SqlDataAdapter(command);
                                        ds = new DataSet();
                                        adp.Fill(ds);
                                        dtUpsZB = ds.Tables[0];
                                    }
                                }
                               
                            }
                            catch (Exception ex)
                            {
                               throw new Exception( string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableIns, cTarTable);

                    var tempTableDel = @"#TempDel" + cTarTable;
                    var createtempsqlDel = string.Format("select {0} into {1}  from {2} Tmp where 1=2", cPKS, tempTableDel, cTarTable);

                    //插入目标表数据
                    var insertsql = string.Format(@"insert into {0}({1}) select {2} from  {3} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {4})  ;", cTarTable, cInsertColumn, cInsertColumn, tempTableIns, cRelation);

                    //如果是条码的，增加 标识插入
                    if (cTBLeiXing=="4")
                    {
                        insertsql = string.Format("SET IDENTITY_Insert {0} ON ", cTarTable) + insertsql + string.Format(" SET IDENTITY_Insert {0} OFF ", cTarTable);
                    }
                    //更新的语句
                    var tempTableUps = @"#TempUps" + cTarTable;
                    var createtempsqlUps = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableUps, cTarTable);
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableUps, cRelation);
                        //子表更新的语句
                        var tempTableUpsZB = @"#TempUpsZB" + cTarTable;
                        var createtempsqlUpsZB = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumnZB, tempTableUpsZB, cTableZB);
                        var updatesqlZB = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunmZB, cTableZB, tempTableUpsZB, cRelationZB);

                    #region 单据替换
                    // 仓库 替换
                    var updatewhIns = "";
                    var updatewhUps = "";                  
                    if (cInsertColumn.ToLower().Contains("cwhcode,"))
                    {
                        updatewhIns = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableIns);
                        updatewhUps = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableUps);
                    }
                    if (cInsertColumn.ToLower().Contains("cOWhCode,"))
                    {
                        updatewhIns = string.Format(" update a set cOWhCode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableIns);
                        updatewhUps = string.Format(" update a set cOWhCode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableUps);
                    }
                    if (cInsertColumn.ToLower().Contains("cIWhCode,"))
                    {
                        updatewhIns = string.Format(" update a set cIWhCode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableIns);
                        updatewhUps = string.Format(" update a set cIWhCode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableUps);
                    }
                    //供应商 替换
                    var updatevenIns = "";
                    var updatevenUps = "";                 
                    if (cInsertColumn.ToLower().Contains("cvencode,"))
                    {
                        updatevenIns = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableIns);
                        updatevenUps = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableUps);
                    }
                    //存货 替换
                    var updateinvIns = "";
                    var updateinvUps = "";                   
                    if (cInsertColumn.ToLower().Contains("cinvcode,"))
                    {
                        updateinvIns = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableIns);
                        updateinvUps = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableUps);
                    }
                    // 子表仓库 替换
                    var updatewhUpsZB = "";                   
                    if (cInsertColumnZB.ToLower().Contains("cwhcode,"))
                    {
                        updatewhUpsZB = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableUpsZB);
                    }
                    //供应商子表
                    var updatevenUpsZB = "";
                    // 替换
                    if (cInsertColumnZB.ToLower().Contains("cvencode,"))
                    {                    
                        updatevenUpsZB = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableUpsZB);
                    }
                    var updateinvUpsZB = "";
                    //存货 替换
                    if (cInsertColumnZB.ToLower().Contains("cinvcode,"))
                    {                       
                        updateinvUpsZB = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableUpsZB);
                    }

                    var deletesql = "";                  
                    deletesql += string.Format(" delete From T from {0} T  where exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableDel, cRelation, cDelFilter);

                    var dropsql = string.Format("DROP TABLE {0}  DROP TABLE {1} DROP TABLE {2} ;", tempTableIns, tempTableDel, tempTableUps);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                    #endregion

                    #region 人员考勤替换
                    //人员考勤检查
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdangan  from zdy_inter_tables WHERE clx = '{0}'
 and bmust=0 and  isnull(cdangan,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunmPerson = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    var insertsqlPerson = string.Format("UPDATE {1} SET {0} FROM {1}   ", cUpdateColunmPerson,  tempTableIns);

                    sql = string.Format(@"  SELECT STUFF((select  ','+ccolumn+'='+cdangan from zdy_inter_tables WHERE clx = '{0}'
 and bmust=0 and isnull(cdangan,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    var cUpdateColunmPerson2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    var updatesqlPerson = string.Format("UPDATE {1} SET {0} FROM {1}   ", cUpdateColunmPerson2, tempTableUps);

                    //考勤时间处理
                    sql = string.Format(@"  SELECT STUFF((select  ','+ccolumn+'='+cdangan from zdy_inter_tables WHERE clx = '{0}'
 and bmust=1 and isnull(cdangan,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                   var cUpdateColunmTime = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    var insertsqlTime = string.Format("UPDATE {1} SET {0} FROM {1}   ", cUpdateColunmTime, tempTableIns);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdangan from zdy_inter_tables WHERE clx = '{0}'
 and bmust=1 and isnull(cdangan,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    var cUpdateColunmTime2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                 
                    var updatesqlTime = string.Format("UPDATE {1} SET {0} FROM {1}   ", cUpdateColunmTime2, tempTableUps);
                    #endregion
                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                    //没有时间戳不更新
                                    if (!string.IsNullOrEmpty(cTimestamp))
                                    {
                                        command.CommandText = createtempsqlUps;
                                        command.ExecuteNonQuery();
                                        //更新
                                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                        {
                                            bulkcopy.BulkCopyTimeout = 300;
                                            bulkcopy.DestinationTableName = tempTableUps;
                                            bulkcopy.WriteToServer(dtUps);
                                            bulkcopy.Close();
                                        }

                                    if (!string.IsNullOrEmpty(updatewhUps))
                                    {
                                        command.CommandText = updatewhUps;
                                        command.ExecuteNonQuery();
                                    }
                                    if (!string.IsNullOrEmpty(updatevenUps))
                                    {
                                        command.CommandText = updatevenUps;
                                        command.ExecuteNonQuery();
                                    }
                                    if (!string.IsNullOrEmpty(updateinvUps))
                                    {
                                        command.CommandText = updateinvUps;
                                        command.ExecuteNonQuery();
                                    }
                                   
                                    if (!string.IsNullOrEmpty(cUpdateColunmPerson2))
                                    {
                                        command.CommandText = updatesqlPerson;
                                        command.ExecuteNonQuery();
                                    }
                                   
                                    if (!string.IsNullOrEmpty(cUpdateColunmTime2))
                                    {
                                        command.CommandText = updatesqlTime;
                                        command.ExecuteNonQuery();
                                    }

                                    //执行更新,有更新栏目的才进行更新
                                    command.CommandTimeout = 300;
                                        if (!string.IsNullOrEmpty(cUpdateColunm))
                                        {
                                            command.CommandText = updatesql;
                                            updateQuery = command.ExecuteNonQuery();
                                        }

                                        //子表的更新
                                        if (!string.IsNullOrEmpty(cTarTables))
                                        {
                                            command.CommandText = createtempsqlUpsZB;
                                            command.ExecuteNonQuery();
                                            //更新
                                            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                            {
                                                bulkcopy.BulkCopyTimeout = 300;
                                                bulkcopy.DestinationTableName = tempTableUpsZB;
                                                bulkcopy.WriteToServer(dtUpsZB);
                                                bulkcopy.Close();
                                            }

                                        if (!string.IsNullOrEmpty(updatewhUpsZB))
                                        {
                                            command.CommandText = updatewhUpsZB;
                                            command.ExecuteNonQuery();
                                        }
                                        if (!string.IsNullOrEmpty(updatevenUpsZB))
                                        {
                                            command.CommandText = updatevenUpsZB;
                                            command.ExecuteNonQuery();
                                        }
                                        if (!string.IsNullOrEmpty(updateinvUpsZB))
                                        {
                                            command.CommandText = updateinvUpsZB;
                                            command.ExecuteNonQuery();
                                        }
                                       
                                        //执行更新,有更新栏目的才进行更新
                                        command.CommandTimeout = 300;
                                            if (!string.IsNullOrEmpty(cUpdateColunm))
                                            {
                                                command.CommandText = updatesqlZB;
                                                command.ExecuteNonQuery();
                                            }
                                        }
                                    
                                }

                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }
                                if (!string.IsNullOrEmpty(updatewhIns))
                                {
                                    command.CommandText = updatewhIns;
                                    command.ExecuteNonQuery();
                                }
                                if (!string.IsNullOrEmpty(updatevenIns))
                                {
                                    command.CommandText = updatevenIns;
                                    command.ExecuteNonQuery();
                                }
                                if (!string.IsNullOrEmpty(updateinvIns))
                                {
                                    command.CommandText = updateinvIns;
                                    command.ExecuteNonQuery();
                                }
                                //考勤人员处理
                                if (!string.IsNullOrEmpty(cUpdateColunmPerson))
                                {
                                    command.CommandText = insertsqlPerson;
                                    command.ExecuteNonQuery();
                                }
                                if (!string.IsNullOrEmpty(cUpdateColunmTime))
                                {
                                    command.CommandText = insertsqlTime;
                                    command.ExecuteNonQuery();
                                }
                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();


                                command.CommandText = createtempsqlDel;
                                command.ExecuteNonQuery();
                                //删除
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableDel;
                                    bulkcopy.WriteToServer(dtDel);
                                    bulkcopy.Close();
                                }
                              
                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();

                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                  select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                  cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableIns, cStampRelation);
                                    command.ExecuteNonQuery();
                                 //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableUps, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    //删除
                                    command.CommandText = string.Format(@" delete  from  T from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} ", cTimestamp, tempTableDel, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region  插入需要转换的业务单据
        /// <summary>
        ///业务单据同步
        /// </summary>
        public string INSVouchZhuan(string cLx, string cFilterOri)
        {
            try
            {
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[ctables],czjbtable,[PK],PK2,CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cupdateproc,cfrom 
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cZjbTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    //来源单据对应的类型，用于取字段
                    string cLxSrc = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKSrc = DbHelper.GetDbString(dt1.Rows[0]["PK2"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                     
                    //取目标表数据的过滤
                    string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //取日期进行查询条件组合
                    //DateTime  dBeginDate  ;
                    sql = string.Format("  SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);

                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                        #region 不使用过滤条件
                        //dBeginDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        ////时间字段 及过滤条件
                        //string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);
                        ////查询条件不为空，按传入的查询条件进行，如果没有按日期进行同步
                        //if (!string.IsNullOrEmpty(cFilterOri))
                        //{

                        //    cFilter += cFilterOri;
                        //}
                        //else
                        //{
                        //    if (!string.IsNullOrEmpty(cDateCol))
                        //    {
                        //        string[] cDateArray = cDateCol.Split('|');
                        //        foreach (string i in cDateArray)
                        //        {
                        //            //权限是5个主键，程序里直接定义
                        //            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dBeginDate);
                        //        }
                        //    }
                        //}
                        #endregion
                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                                

                    //目标表时间戳的处理
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    //源表时间戳
                    string cTimestampSrc = DbHelper.GetDbString(dt1.Rows[0]["cupdateproc"]);
                    
                    string cStampRelation = "";                
                     cStampRelation += string.Format(" and Tmp.cpk=T.{0}", cPK);

                    //更新目标
                    string cRelation = "";
                    cRelation += string.Format(" and  Tmp.{0}=T.{1}  ", cPKSrc, cPK);

                    //获取源表，主键的处理,用于获取源表的新增、删除、更新数据
                    string cRelationSrc = "";
                    cRelationSrc += string.Format(" and  Tmp.{0}=T.{1}  ", cPK, cPKSrc);


                    //源表的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'  and budaoru=0
   FOR XML PATH('')),1,1,'')", cLxSrc);
                    string cInsertColumnSrc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                   
                    string cSelectColumnSrc = cInsertColumnSrc;

                
                    //查询的字段，如果时间戳不为空，增加时间戳
                    if (!string.IsNullOrEmpty(cTimestampSrc))
                    {
                        cSelectColumnSrc += string.Format(" ,convert(bigint,{0}) as {0}", cTimestampSrc);
                    }

                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' AND budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cname  from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}'  AND budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cValueColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+cname from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //取目标表的id
                    //创建源库临时表，存放目标表的id
                    var srcTempTable = @"#TempSrc" + cTarTable;
                    var srcCreatetempsql = string.Format("select {0},T.ctimestamp into {1} from {2} Tmp left join zdy_zd_stamp T ON T.clx='{3}' where 1=2 ", cPK, srcTempTable, cTarTable, cLx);
                    //获取取目标表的id，减去1000000000
                    var tarSelectsql = "";
                    if (cLx.StartsWith("委外"))
                   {
                        tarSelectsql = string.Format(@"select {0}-1000000000 as {0},Tmp.ctimestamp from {1} T left join zdy_zd_stamp Tmp ON Tmp.clx='{2}' {3}   where 1=1 {4}",
                       cPK, cTarTable, cLx, cStampRelation, cFilterSrc);
                    }
                    else
                    {
                        tarSelectsql = string.Format(@"select {0} ,Tmp.ctimestamp from {1} T left join zdy_zd_stamp Tmp ON Tmp.clx='{2}' {3}   where 1=1 {4}",
                            cPK, cTarTable, cLx, cStampRelation, cFilterSrc);
                    }
                    DataTable tardt = DbHelper2.ExecuteTable(tarSelectsql, cConStrB);
                    //存储
                    DataTable dtIns = new DataTable();
                    DataTable dtDel = new DataTable();
                    DataTable dtUps = new DataTable();
             
                    //查询源表和目标表的差异，insert表， delete 表和更新表
                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = srcCreatetempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = srcTempTable;
                                    bulkcopy.WriteToServer(tardt);
                                    bulkcopy.Close();
                                }

                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  " +
                        "where not exists(select 1 from {2} Tmp where 1=1  {3} ) {4}", cSelectColumnSrc, cZjbTable, srcTempTable, cRelationSrc, cFilter);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];

                                var srcDelectsql = string.Format("select {0} from  {1} Tmp  " +
                        "where not exists(select 1 from {2} T where 1=1 {3} {4}) ", cPK,srcTempTable, cZjbTable, cRelationSrc,cFilter);
                                command.CommandText = srcDelectsql;
                                adp = new SqlDataAdapter(command);
                                ds = new DataSet();
                                adp.Fill(ds);
                                dtDel = ds.Tables[0];

                                //单据更新状态下：有时间戳，才进行更新。
                                if (!string.IsNullOrEmpty(cTimestampSrc))
                                {
                                    var srcUpdatesql = string.Format("select {0} from  {1} T  " +
                        "where exists(select 1 from {2} Tmp where convert(bigint,T.{4})<>Tmp.ctimestamp {3}) ", cSelectColumnSrc, cZjbTable, srcTempTable, cRelationSrc, cTimestampSrc);
                                    command.CommandText = srcUpdatesql;
                                    adp = new SqlDataAdapter(command);
                                    ds = new DataSet();
                                    adp.Fill(ds);
                                    dtUps = ds.Tables[0];
                                }

                            }
                            catch (Exception ex)
                            {
                                throw new Exception(string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumnSrc, tempTableIns, cZjbTable);

                    var tempTableDel = @"#TempDel" + cTarTable;
                    var createtempsqlDel = string.Format("select {0} into {1}  from {2} Tmp where 1=2", cPKSrc, tempTableDel, cZjbTable);

                    //插入目标表数据
                    var insertsql = string.Format(@"insert into {0}({1}) select {2} from  {3} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {4} )  ;", cTarTable, cInsertColumn,cValueColumn, tempTableIns, cRelation);
                    //更新的语句
                    var tempTableUps = @"#TempUps" + cTarTable;
                    var createtempsqlUps = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumnSrc, tempTableUps, cZjbTable);
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableUps, cRelation);
                    var deletesql = "";
                    deletesql += string.Format(" delete From T from {0} T  where exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableDel, cRelation, cDelFilter);

                    var dropsql = string.Format("DROP TABLE {0}  DROP TABLE {1} DROP TABLE {2} ;", tempTableIns, tempTableDel, tempTableUps);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestampSrc);

                    }
                    #region 单据替换
                    // 仓库 替换
                    var updatewhIns = "";
                    var updatewhUps = "";
                    if (cInsertColumn.ToLower().Contains("cwhcode,"))
                    {
                        updatewhIns = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableIns);
                        updatewhUps = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableUps);
                    }
                    //供应商 替换
                    var updatevenIns = "";
                    var updatevenUps = "";
                    if (cInsertColumn.ToLower().Contains("cvencode,"))
                    {
                        updatevenIns = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableIns);
                        updatevenUps = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableUps);
                    }
                    //存货 替换
                    var updateinvIns = "";
                    var updateinvUps = "";
                    if (cInsertColumn.ToLower().Contains("cinvcode,"))
                    {
                        updateinvIns = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableIns);
                        updateinvUps = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableUps);
                    }
                  

                 
                    #endregion


                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                //没有时间戳不更新
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    command.CommandText = createtempsqlUps;
                                    command.ExecuteNonQuery();
                                    //更新
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableUps;
                                        bulkcopy.WriteToServer(dtUps);
                                        bulkcopy.Close();
                                    }

                                    if (!string.IsNullOrEmpty(updatewhUps))
                                    {
                                        command.CommandText = updatewhUps;
                                        command.ExecuteNonQuery();
                                    }
                                    if (!string.IsNullOrEmpty(updatevenUps))
                                    {
                                        command.CommandText = updatevenUps;
                                        command.ExecuteNonQuery();
                                    }
                                    if (!string.IsNullOrEmpty(updateinvUps))
                                    {
                                        command.CommandText = updateinvUps;
                                        command.ExecuteNonQuery();
                                    }

                                    //执行更新,有更新栏目的才进行更新
                                    command.CommandTimeout = 300;
                                    if (!string.IsNullOrEmpty(cUpdateColunm))
                                    {
                                        command.CommandText = updatesql;
                                        updateQuery = command.ExecuteNonQuery();
                                    }
                                }

                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }
                                if (!string.IsNullOrEmpty(updatewhIns))
                                {
                                    command.CommandText = updatewhIns;
                                    command.ExecuteNonQuery();
                                }
                                if (!string.IsNullOrEmpty(updatevenIns))
                                {
                                    command.CommandText = updatevenIns;
                                    command.ExecuteNonQuery();
                                }
                                if (!string.IsNullOrEmpty(updateinvIns))
                                {
                                    command.CommandText = updateinvIns;
                                    command.ExecuteNonQuery();
                                }

                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();

                               

                                //删除
                                command.CommandText = createtempsqlDel;
                                command.ExecuteNonQuery();                                
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableDel;
                                    bulkcopy.WriteToServer(dtDel);
                                    bulkcopy.Close();
                                }

                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();

                                //生产订单把时间表加进去
                                if (cLx == "委外订单转生产子表")
                                {
                                    command.CommandText = string.Format(@"INSERT INTO  mom_morder(moid,modid,StartDate,DueDate)
SELECT moid+1000000000,modetailsid + 1000000000,dArriveDate,dArriveDate FROM {0}", tempTableIns);
                                    command.ExecuteNonQuery();

                                   //更新componentid
                                        command.CommandText = string.Format(@"
UPDATE a SET PartId=b.PartId
FROM dbo.mom_orderdetail  a,bas_part b  
WHERE a.InvCode=b.InvCode AND ISNULL(a.Free6,'')=ISNULL(b.Free6,'') and a.PartId IS NULL");
                                        command.ExecuteNonQuery();
                                }
                                else if  (cLx == "委外订单转生产材料表")
                                    {
                                        command.CommandText = string.Format(@"UPDATE a SET a.ComponentId=b.PartId
FROM dbo.mom_moallocate  a,bas_part b  
WHERE a.InvCode=b.InvCode AND ISNULL(a.Free6,'')=ISNULL(b.Free6,'') and a.ComponentId IS NULL");
                                        command.ExecuteNonQuery();
                                    }
                                else if (cLx == "其他出库转材料出库子表")
                                {
                                    command.CommandText = string.Format(@"  update a set cvouchtype='11' FROM dbo.InvPosition a,rdrecords11 b WHERE b.cbsysbarcode='st11'
  AND a.RdsID=b.AutoID and cvouchtype='09'");
                                    command.ExecuteNonQuery();
                                }


                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,ctimestamp)
                                  select '{0}',{1},{2}  from {3} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 and T.cpk=Tmp.{4} and clx='{0}') ",
                                  cLx, cPKSrc, cTimestampSrc, tempTableIns,cPKSrc);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} and T.ctimestamp<>Tmp.{0}", cTimestampSrc, tempTableUps, cLx, cPKSrc);
                                    command.ExecuteNonQuery();

                                    //删除
                                    command.CommandText = string.Format(@" delete  from  T from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} ", cTimestampSrc, tempTableDel, cLx, cPKSrc);
                                    command.ExecuteNonQuery();

                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region  插入业务单据MES
        /// <summary>
        ///业务单据同步
        /// </summary>
        public string INSVouchMES(string cLx, string cFilterOri)
        {
            try
            {
                //CheckTimeTable();
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[ctables],[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cfrom
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTables = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //取目标表数据的过滤
                    string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //取日期进行查询条件组合
                    //DateTime  dBeginDate  ;
                    sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);


                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }

                        #region 不使用过滤条件
                        //dBeginDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        ////时间字段 及过滤条件
                        //string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);
                        ////查询条件不为空，按传入的查询条件进行，如果没有按日期进行同步
                        //if (!string.IsNullOrEmpty(cFilterOri))
                        //{

                        //    cFilter += cFilterOri;
                        //}
                        //else
                        //{
                        //    if (!string.IsNullOrEmpty(cDateCol))
                        //    {
                        //        string[] cDateArray = cDateCol.Split('|');
                        //        foreach (string i in cDateArray)
                        //        {
                        //            //权限是5个主键，程序里直接定义
                        //            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dBeginDate);
                        //        }
                        //    }
                        //}
                        #endregion

                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                    //主键的处理
                    string cRelation = "";
                    string cPKS = "";  //主键的组合
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                        cPKS += string.Format("Tmp.{0},", i);
                    }
                    cPKS = cPKS.Substring(0, cPKS.Length - 1);

                    //时间戳的处理
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cStampRelation = "";
                    string[] newPKAarray = new string[5];
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //不足5位主键的补足5位，后面的补空‘’
                        for (int i = 0; i < 5; i++)
                        {
                            if (i < cPKArray.Length)
                            {
                                newPKAarray[i] = cPKArray[i];
                                if (i == 0)
                                {
                                    cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                }
                                else
                                {
                                    cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                }
                            }
                            else
                            {
                                newPKAarray[i] = "0";
                            }
                        }
                    }

                    //插入的字段和值
                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn  from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                    }

                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));



                    //取目标表的id
                    //创建源库临时表，存放目标表的id
                    var srcTempTable = @"#TempSrc" + cTarTable;
                    var srcCreatetempsql = string.Format("select {0},T.ctimestamp into {1} from {2} Tmp left join zdy_zd_stamp T ON T.clx='{3}' where 1=2  ", cPKS, srcTempTable, cTarTable, cLx);
                    //获取取目标表的id
                    var srcselectsql = string.Format(@"select {0},T.ctimestamp from {1} Tmp left join zdy_zd_stamp T ON T.clx='{2}' {3}   where 1=1 {4} {5}",
                        cPKS, cTarTable, cLx, cStampRelation, cFilter, cFilterSrc);
                    DataTable srcdt = DbHelper2.ExecuteTable(srcselectsql, cConStrB);
                    //存储
                    DataTable dtIns = new DataTable();
                    DataTable dtDel = new DataTable();
                    DataTable dtUps = new DataTable();
                 
                    //查询源表和目标表的差异，insert表， delete 表和更新表

                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = srcCreatetempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = srcTempTable;
                                    bulkcopy.WriteToServer(srcdt);
                                    bulkcopy.Close();
                                }

                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  " +
                        "where not exists(select 1 from {2} Tmp where 1=1 {3}) {4}", cSelectColumn, cTarTable, srcTempTable, cRelation, cFilter);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];

                                var srcDelectsql = string.Format("select {0} from  {1} Tmp  " +
                        "where not exists(select 1 from {2} T where 1=1 {3}) ", cPKS, srcTempTable, cTarTable, cRelation);
                                command.CommandText = srcDelectsql;
                                adp = new SqlDataAdapter(command);
                                ds = new DataSet();
                                adp.Fill(ds);
                                dtDel = ds.Tables[0];

                                //单据更新状态下：有时间戳，才进行更新。
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    var srcUpdatesql = string.Format("select {0} from  {1} T  " +
                        "where exists(select 1 from {2} Tmp where convert(bigint,T.{4})<>Tmp.ctimestamp {3} ) ", cSelectColumn, cTarTable, srcTempTable, cRelation, cTimestamp);
                                    command.CommandText = srcUpdatesql;
                                    adp = new SqlDataAdapter(command);
                                    ds = new DataSet();
                                    adp.Fill(ds);
                                    dtUps = ds.Tables[0];
                                }

                            }
                            catch (Exception ex)
                            {
                                throw new Exception(string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableIns, cTarTable);

                    var tempTableDel = @"#TempDel" + cTarTable;
                    var createtempsqlDel = string.Format("select {0} into {1}  from {2} Tmp where 1=2", cPKS, tempTableDel, cTarTable);

                    //插入目标表数据

                    

                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableIns, cRelation);

                   
                    insertsql = string.Format("SET IDENTITY_INSERT {0} ON  ", cTarTable) + insertsql + string.Format(" SET IDENTITY_Insert {0} OFF ", cTarTable);
              
                    //更新的语句
                    var tempTableUps = @"#TempUps" + cTarTable;
                    var createtempsqlUps = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableUps, cTarTable);
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableUps, cRelation);
                    var deletesql = "";
                    deletesql += string.Format(" delete From T from {0} T  where exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableDel, cRelation, cDelFilter);
                    var dropsql = string.Format("DROP TABLE {0}  DROP TABLE {1} DROP TABLE {2} ;", tempTableIns, tempTableDel, tempTableUps);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                //没有时间戳不更新
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    command.CommandText = createtempsqlUps;
                                    command.ExecuteNonQuery();
                                    //更新,保留原identity值
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity,null))
                                    {
                                       
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableUps;
                                        bulkcopy.WriteToServer(dtUps);
                                        bulkcopy.Close();
                                    }

                                   
                                    //执行更新,有更新栏目的才进行更新
                                    command.CommandTimeout = 300;
                                    if (!string.IsNullOrEmpty(cUpdateColunm))
                                    {
                                        command.CommandText = updatesql;
                                        updateQuery = command.ExecuteNonQuery();
                                    }

                          

                                }

                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }
                                
                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();


                                command.CommandText = createtempsqlDel;
                                command.ExecuteNonQuery();
                                //删除
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableDel;
                                    bulkcopy.WriteToServer(dtDel);
                                    bulkcopy.Close();
                                }

                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();

                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                  select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                  cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableIns, cStampRelation);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableUps, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    //删除
                                    command.CommandText = string.Format(@" delete  from  T from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} ", cTimestamp, tempTableDel, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region 插入考勤数据
        /// <summary>
        /// 插入考勤数据
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <returns></returns>
        public string InsKaoqin( string cLx)
        {
            try
            {
                string sql = string.Format("  SELECT DATEADD(SECOND,{2},DUPDATETIME) FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx,0-iInterval);
                DateTime dBeginDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                DateTime dEndDate = DateTime.Now;
                TimeSpan sp = dEndDate.Subtract(dBeginDate);
                int days = sp.Days;  //值为1
                if (days >= 30)
                {
                    dEndDate = dBeginDate.AddDays(30);
                }
                QueryTime queryTime = new QueryTime();
                queryTime.starttime = dBeginDate.ToString("yyyy-MM-dd HH:mm:ss");
                queryTime.endtime = dEndDate.ToString("yyyy-MM-dd HH:mm:ss");
                string queryJson = JsonConvert.SerializeObject(queryTime);
                //WriteLog writeLog = new WriteLog();
                //writeLog.WriteLogs(queryJson);
                //地址
                string _url = "http://192.168.1.8:6660/api/v2/transaction/get/?key=ec34cj5u0bcxxyal1ats692csi2tizjphfknp629d9aw";
                //json参数
                string jsonParam = queryJson;
                var request = (HttpWebRequest)WebRequest.Create(_url);
                request.Method = "POST";
                request.ContentType = "application/json;charset=UTF-8";
                byte[] byteData = Encoding.UTF8.GetBytes(jsonParam);
                int length = byteData.Length;
                request.ContentLength = length;
                Stream writer = request.GetRequestStream();
                writer.Write(byteData, 0, length);
                writer.Close();
                var response = (HttpWebResponse)request.GetResponse();
                var responseString = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding("utf-8")).ReadToEnd();

                KaoQinFanHui kaoQinFanHui = JsonConvert.DeserializeObject<KaoQinFanHui>(responseString);
                if (kaoQinFanHui.ret == 0)
                {
                    Data data = kaoQinFanHui.data;
                    if (data.count > 0)
                    {
                        List<ItemsItem> itemsItem = data.items;
                        DataTable dt = TableListConverter.ListToTable(itemsItem);

                        var tempTableName = @"#TempZDY_ZD_ZKORIDATA" ;
                        var createtempsql = string.Format("select * into {0} from ZDY_ZD_ZKORIDATA where 1=2",  tempTableName);
                        //更新时间
                        var updatesql = "";
                        var insertsql = ""; 
                        var insertsql2 = "";
                        var updatesql2 = ""; //更新日考勤单据，判断今日有没有打卡
                        var dropsql ="";

                        //生成随机的新时间
                        updatesql = string.Format(@"UPDATE B SET ilx= a.ilx, newtime = case when  (a.ilx<9) then DATEADD(S, CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(NEWID())),9))*90/100000000 ,A.dcaltime) else null end,drealdate = a.ddate    from {0} B, zdy_zd_pbperiod A
WHERE A.cpsn_num = B.pin  AND B.checktime >= a.dbegintime and B.checktime < a.dendtime", tempTableName);
                            //按新时间进行插入,bduty = 1,代表更改的
                        insertsql = string.Format(@"insert into hr_tm_OriCardData(urecordid,cpsn_num,vCardNo,ddatetime,bManual,iflag,cOptPsn_Num,dsystime,bEffect,bAuditFlag,cAuditorNum,dAuditTime,nMachine_Num,cSource,cStatus,cExamineApproveType,bduty,cMobileSiteAddress,dOldDateTime)
SELECT  newid(),pin ,pin,newtime ,0,0,'demo',newtime,1,1,'demo',convert(varchar(20),newtime,20),1,0,2,2,1,a.ilx,drealdate
from {0} a 
INNER JOIN zdy_zd_pbperiod b ON b.cpsn_num = a.pin  AND a.checktime >= b.dbegintime and a.checktime < b.dendtime
 where (a.ilx<9) AND ISNULL(newtime,'')<>''
 and not exists(select 1 from hr_tm_OriCardData c where b.cpsn_num = c.cpsn_num and  c.ddatetime BETWEEN b.dcaltime AND b.dcalendtime)", tempTableName);
                            //按新时间进行插入,bduty = 0,代表原始正常的
                       insertsql2 = string.Format(@"insert into hr_tm_OriCardData(urecordid,cpsn_num,vCardNo,ddatetime,bManual,iflag,cOptPsn_Num,dsystime,bEffect,bAuditFlag,cAuditorNum,dAuditTime,nMachine_Num,cSource,cStatus,cExamineApproveType,bduty,cMobileSiteAddress,dOldDateTime)
select  newid(),pin ,pin,checktime ,0,0,'demo',checktime,1,1,'demo',convert(varchar(20),checktime,20),1,0,2,2,0,ilx,drealdate
from {0} where (ilx>=9) ", tempTableName);
                           //有newtime 更新newtime,没有的更新checktime
                        updatesql2 = string.Format(@"update a set nDedAbsentMinute=1 from hr_tm_dayresult a,{0} b where a.cPsn_Num =b.pin and a.dDutyDate=b.drealdate
and  ((ilx<9 AND ISNULL(newtime,'')<>'') or (ilx>=9))
 ", tempTableName);
                          
                            dropsql = string.Format("DROP TABLE {0};", tempTableName);
                        
                        using (SqlConnection conn = new SqlConnection(cConStrB))
                        {
                            using (SqlCommand command = new SqlCommand("", conn))
                            {
                                try
                                {
                                    conn.Open();
                                    //创建临时表
                                    command.CommandText = createtempsql;
                                    command.ExecuteNonQuery();
                                    //插入源表数据
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableName;
                                        bulkcopy.WriteToServer(dt);
                                        bulkcopy.Close();
                                    }

                                    //执行更新
                                    command.CommandTimeout = 300;
                                    if (!string.IsNullOrEmpty(insertsql2))
                                    {
                                        command.CommandText = updatesql;
                                        int updateQuery = command.ExecuteNonQuery();
                                    }
                                    //执行插入U8考勤
                                    command.CommandText = insertsql;
                                    int insertQuery = command.ExecuteNonQuery();

                                    //执行插入U8考勤
                                    if (!string.IsNullOrEmpty(insertsql2))
                                    {
                                        command.CommandText = insertsql2;
                                        int insertQuery2 = command.ExecuteNonQuery();
                                    }
                                    //更新当天是否出勤
                                    if (!string.IsNullOrEmpty(updatesql2))
                                    {
                                        command.CommandText = updatesql2;
                                        command.ExecuteNonQuery();
                                    }


                                    //删除临时表 
                                    command.CommandText = dropsql;
                                    int delQuery = command.ExecuteNonQuery();

                                    cError = string.Format("查询并导入{0}条数据 ", insertQuery);

                                  

                                }
                                catch (Exception ex)
                                {
                                    cError = string.Format("error:{0}", ex.Message + ex.StackTrace);

                                }
                                finally
                                {
                                    conn.Close();
                                }
                            }
                        }
                        //回写更新的时间
                        sql = string.Format(" update  a  set DUPDATETIME='{2}' FROM ZDY_INTER_XIETONG a WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx, dEndDate);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);

                        sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", queryTime.starttime+"-"+ queryTime.endtime+":"+data.count.ToString(), cLx, iDBID);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                    }

                    //删除重复的考勤
                    sql = string.Format(@"
           DELETE FROM a  from hr_tm_OriCardData a,(SELECT  cPsn_Num,dDateTime FROM hr_tm_OriCardData 
where dDateTime>='{0}'
GROUP BY cPsn_Num,dDateTime
HAVING COUNT(*)>1) b,
(SELECT cPsn_Num,dDateTime,MIN(convert(varchar(50), urecordid)) minid FROM hr_tm_OriCardData 
where dDateTime>='{0}'
GROUP BY cPsn_Num,dDateTime
HAVING COUNT(*)>1) c 
where  a.cPsn_Num=b.cPsn_Num AND a.dDateTime = b.dDateTime
  AND a.cPsn_Num=c.cPsn_Num AND a.dDateTime=c.dDateTime AND a.uRecordId<>minid", dBeginDate);
                    DbHelper2.ExecuteNonQuery(sql, cConStrB);
                }
                else
                {
                    //失败记录
                    sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    ([DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(getdate(),'{0}','{1}')", kaoQinFanHui.msg, cLx);
                    DbHelper2.ExecuteNonQuery(sql, cConStr);

                }
                return kaoQinFanHui.msg;
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;

            }


        }
        #endregion

        #region 插入考勤数据A
        /// <summary>
        /// 插入考勤数据
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <returns></returns>
        public string InsKaoqinA(string cLx)
        {
            try
            {
    
                string sql = string.Format("  SELECT DATEADD(SECOND,{2},DUPDATETIME) FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx, 0 - iInterval);
                //string sql = string.Format("  SELECT DATEADD(M,DUPDATETIME FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx);
                DateTime dBeginDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                DateTime dEndDate = DateTime.Now;
                QueryTime queryTime = new QueryTime();
                queryTime.starttime = dBeginDate.ToString("yyyy-MM-dd HH:mm:ss");
                queryTime.endtime = dEndDate.ToString("yyyy-MM-dd HH:mm:ss");
                string queryJson = JsonConvert.SerializeObject(queryTime);

                //地址
                string _url = "http://192.168.1.8:6660/api/v2/transaction/get/?key=ec34cj5u0bcxxyal1ats692csi2tizjphfknp629d9aw";
                //json参数
                string jsonParam = queryJson;
                var request = (HttpWebRequest)WebRequest.Create(_url);
                request.Method = "POST";
                request.ContentType = "application/json;charset=UTF-8";
                byte[] byteData = Encoding.UTF8.GetBytes(jsonParam);
                int length = byteData.Length;
                request.ContentLength = length;
                Stream writer = request.GetRequestStream();
                writer.Write(byteData, 0, length);
                writer.Close();
                var response = (HttpWebResponse)request.GetResponse();
                var responseString = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding("utf-8")).ReadToEnd();

                KaoQinFanHui kaoQinFanHui = JsonConvert.DeserializeObject<KaoQinFanHui>(responseString);
                if (kaoQinFanHui.ret == 0)
                {
                    Data data = kaoQinFanHui.data;
                    if (data.count > 0)
                    {
                        List<ItemsItem> itemsItem = data.items;
                        DataTable dt = TableListConverter.ListToTable(itemsItem);

                        var tempTableName = @"#TempZDY_ZD_ZKORIDATA";
                        var createtempsql = string.Format("select * into {0} from ZDY_ZD_ZKORIDATA where 1=2", tempTableName);
                        //更新时间
                        var updatesql = "";
                        var insertsql = "";
                        var insertsql2 = "";
                        var updatesql2 = ""; //更新日考勤单据，判断今日有没有打卡
                        var dropsql = "";

                            insertsql = string.Format(@"insert into hr_tm_OriCardData(urecordid,cpsn_num,vCardNo,ddatetime,bManual,iflag,cOptPsn_Num,dsystime,bEffect,bAuditFlag,cAuditorNum,dAuditTime,nMachine_Num,cSource,cStatus,cExamineApproveType,bduty)
select  newid(),pin ,pin,checktime ,0,0,'demo',checktime,1,1,'demo',checktime,1,0,2,2,1
from {0} ", tempTableName);
                            updatesql2 = string.Format(@"update a set nDedAbsentMinute=1 from hr_tm_dayresult a,{0} b where a.cPsn_Num =b.pin and a.dDutyDate=CONVERT(varchar(20), b.checktime,23)
 ", tempTableName);
                            dropsql = string.Format("DROP TABLE {0};", tempTableName);
                        
               
                        using (SqlConnection conn = new SqlConnection(cConStrB))
                        {
                            using (SqlCommand command = new SqlCommand("", conn))
                            {
                                try
                                {
                                    conn.Open();
                                    //创建临时表
                                    command.CommandText = createtempsql;
                                    command.ExecuteNonQuery();
                                    //插入源表数据
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableName;
                                        bulkcopy.WriteToServer(dt);
                                        bulkcopy.Close();
                                    }

                                    //执行更新
                                    command.CommandTimeout = 300;
                                    if (!string.IsNullOrEmpty(insertsql2))
                                    {
                                        command.CommandText = updatesql;
                                        int updateQuery = command.ExecuteNonQuery();
                                    }
                                    //执行插入U8考勤
                                    command.CommandText = insertsql;
                                    int insertQuery = command.ExecuteNonQuery();

                                    //执行插入U8考勤
                                    if (!string.IsNullOrEmpty(insertsql2))
                                    {
                                        command.CommandText = insertsql2;
                                        int insertQuery2 = command.ExecuteNonQuery();
                                    }
                                    //更新当天是否出勤
                                    if (!string.IsNullOrEmpty(updatesql2))
                                    {
                                        command.CommandText = updatesql2;
                                        command.ExecuteNonQuery();
                                    }


                                    //删除临时表 
                                    command.CommandText = dropsql;
                                    int delQuery = command.ExecuteNonQuery();

                                    cError = string.Format("查询并导入{0}条数据 ", insertQuery);



                                }
                                catch (Exception ex)
                                {
                                    cError = string.Format("error:{0}", ex.Message + ex.StackTrace);

                                }
                                finally
                                {
                                    conn.Close();
                                }
                            }
                        }
                        //回写更新的时间
                        sql = string.Format(" update  a  set DUPDATETIME='{2}' FROM ZDY_INTER_XIETONG a WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx, dEndDate);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);

                        sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", queryTime.starttime + "-" + queryTime.endtime + ":" + data.count.ToString(), cLx, iDBID);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);


                        
                        //删除重复的考勤
                        sql = string.Format(@"
           DELETE FROM a  from hr_tm_OriCardData a,(SELECT  cPsn_Num,dDateTime FROM hr_tm_OriCardData 
where dDateTime>='{0}'
GROUP BY cPsn_Num,dDateTime
HAVING COUNT(*)>1) b,
(SELECT cPsn_Num,dDateTime,MIN(convert(varchar(50), urecordid)) minid FROM hr_tm_OriCardData 
where dDateTime>='{0}'
GROUP BY cPsn_Num,dDateTime
HAVING COUNT(*)>1) c 
where  a.cPsn_Num=b.cPsn_Num AND a.dDateTime = b.dDateTime
  AND a.cPsn_Num=c.cPsn_Num AND a.dDateTime=c.dDateTime AND a.uRecordId<>minid", dBeginDate);
                        DbHelper2.ExecuteNonQuery(sql, cConStrB);
                    }

                }
                else
                {
                    //失败记录
                    sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    ([DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(getdate(),'{0}','{1}')", kaoQinFanHui.msg, cLx);
                    DbHelper2.ExecuteNonQuery(sql, cConStr);

                }
                return kaoQinFanHui.msg;
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;

            }


        }
        #endregion

        //更新考勤的同步时间
        public string ChangeUpDateTime(string cLx, string cFilter)
        {
            int iDays = DbHelper.GetDbInt(cFilter);
            if (iDays == 0)
            {
                iDays = -1;
            }
            else
            {
                iDays =0 - iDays;
            }

            string sql =  string.Format("update  ZDY_INTER_XIETONG set DUPDATETIME =DATEADD(Day,{2},DUPDATETIME)   FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx, iDays);
            DbHelper2.ExecuteNonQuery(sql, cConStr);
            return "ok";
        }

        #region 处理昨天的考勤数据，缺勤的补上
        /// <summary>
        /// 插入考勤数据,从上次更新的前一天，到本次更新的前一天
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <param name="iJiange">删除日期的间隔</param>
        /// <returns></returns>
        public string UpdateKaoqin(string cLx)
        {
            try
            {
     
                   //插入多班次，有上下班卡，无中间卡的
                   string sql = string.Format(@"
         insert into hr_tm_OriCardData(urecordid,cpsn_num,vCardNo,ddatetime,bManual,iflag,cOptPsn_Num,dsystime,bEffect,bAuditFlag,cAuditorNum,dAuditTime,nMachine_Num,cSource,cStatus,cExamineApproveType,bduty,dOldDateTime)
select  newid(),a.cpsn_num ,a.cpsn_num, newtime  =DATEADD(S, CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(NEWID())),9))*90/100000000  ,A.dbegintime)   ,0,0,'demo1',
DATEADD(S, CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(NEWID())),9))*90/100000000  ,A.dbegintime),1,1,'demo',
DATEADD(S, CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(NEWID())),9))*90/100000000  ,A.dbegintime) ,1,0,2,2,1 ,a.ddate
from zdy_zd_pbperiod a
left join hr_tm_OriCardData b on a.cpsn_num=b.cPsn_Num and a.dbegintime<=b.dDateTime and  a.dendtime>b.dDateTime
where  a.ddate>=DATEADD(d, -7, getdate())  and a.ddate<getdate()  and (a.ilx=12 or a.ilx=13) and b.cPsn_Num is null
 
and ( exists  ( 
---同天的卡，在中间卡之前
select 1 from zdy_zd_pbperiod c
inner join hr_tm_OriCardData d on c.cpsn_num=d.cPsn_Num and c.dbegintime<=d.dDateTime and  c.dendtime>d.dDateTime
where c.ddate=a.ddate and a.cpsn_num=c.cpsn_num  and d.dDateTime<a.dbegintime 
 ) or exists( 
select 1 from zdy_zd_pbperiod c
inner join hr_tm_signCardData d on c.cpsn_num=d.cPsn_Num and c.dbegintime<=d.dDateTime and  c.dendtime>d.dDateTime
where c.ddate=a.ddate and a.cpsn_num=c.cpsn_num  and d.dDateTime<a.dbegintime 
 ) )
and  (exists  (
--同天的卡，在中间卡之后
select 1 from zdy_zd_pbperiod c
inner join hr_tm_OriCardData d on c.cpsn_num=d.cPsn_Num and c.dbegintime<=d.dDateTime and  c.dendtime>d.dDateTime
where c.ddate=a.ddate and a.cpsn_num=c.cpsn_num and d.dDateTime>a.dendtime )
or exists( 
select 1 from zdy_zd_pbperiod c
inner join hr_tm_signCardData d on c.cpsn_num=d.cPsn_Num and c.dbegintime<=d.dDateTime and  c.dendtime>d.dDateTime
where c.ddate=a.ddate and a.cpsn_num=c.cpsn_num and d.dDateTime>a.dendtime 
 ))
");
                    DbHelper2.ExecuteNonQuery(sql, cConStrB);

                sql = string.Format(@" update hr_tm_OriCardData set dSysTime=dDateTime where dDateTime<>dSysTime
        
");
                DbHelper2.ExecuteNonQuery(sql, cConStrB);
                //                and exists(
                //                select 1 from zdy_zd_pbperiod c
                //                inner
                //                         join hr_tm_OriCardData d on c.cpsn_num = d.cPsn_Num and c.dbegintime <= d.dDateTime and c.dendtime > d.dDateTime
                //where c.ddate = a.ddate and a.cpsn_num = c.cpsn_num and c.ilx = 10  )
                //and exists(
                //select 1 from zdy_zd_pbperiod c
                //inner
                //         join hr_tm_OriCardData d on c.cpsn_num = d.cPsn_Num and c.dbegintime <= d.dDateTime and c.dendtime > d.dDateTime
                //where c.ddate = a.ddate and a.cpsn_num = c.cpsn_num and c.ilx = 11  )
                //删除重复的考勤
                //                    sql = string.Format(@"
                //            delete  from hr_tm_OriCardData where urecordid in 
                //(SELECT MAX(convert(varchar(50), urecordid)) FROM hr_tm_OriCardData 
                //where dDateTime>= DATEADD(d,-3,getdate())
                //GROUP BY cPsn_Num,dDateTime
                //HAVING COUNT(*)>1)");
                sql = string.Format(@"
           DELETE FROM a from hr_tm_OriCardData a,(SELECT  cPsn_Num, dDateTime FROM hr_tm_OriCardData
where dDateTime >= DATEADD(d,-3,getdate())
GROUP BY cPsn_Num, dDateTime
HAVING COUNT(*) > 1) b,
(SELECT cPsn_Num, dDateTime, MIN(convert(varchar(50), urecordid)) minid FROM hr_tm_OriCardData
  where dDateTime >=DATEADD(d,-3,getdate())
GROUP BY cPsn_Num,dDateTime
HAVING COUNT(*) > 1) c
  where  a.cPsn_Num = b.cPsn_Num AND a.dDateTime = b.dDateTime
  AND a.cPsn_Num = c.cPsn_Num AND a.dDateTime = c.dDateTime AND a.uRecordId<> minid"); 
                    DbHelper2.ExecuteNonQuery(sql, cConStrB);
                    //上下班期间，有正常打卡的，有自动填补的，删除填补的卡
                    sql = string.Format(@"  delete from b from zdy_zd_pbperiod a, hr_tm_OriCardData b where bDuty = 1
and a.cpsn_num = b.cPsn_Num and a.dbegintime <= b.dDateTime and a.dendtime > b.dDateTime
and b.dDateTime >= DATEADD(d, -3, getdate())
and exists(select 1 from hr_tm_OriCardData c
where  a.cpsn_num = c.cPsn_Num and bDuty = 0
and  a.dbegintime <= c.dDateTime and a.dendtime > c.dDateTime)");
                    DbHelper2.ExecuteNonQuery(sql, cConStrB);


                sql = string.Format(@" delete from b from zdy_zd_pbperiod a, hr_tm_OriCardData b where bDuty = 1
and a.cpsn_num = b.cPsn_Num and a.dbegintime <= b.dDateTime and a.dendtime > b.dDateTime
and b.dDateTime >= DATEADD(d, -3, getdate())
and exists(select 1 from hr_tm_OriCardData c
where bDuty = 0
and a.cpsn_num = c.cPsn_Num and a.dbegintime <= c.dDateTime and a.dendtime > c.dDateTime)");
                DbHelper2.ExecuteNonQuery(sql, cConStrB);
                //回写更新的时间
                sql = string.Format(" update  a  set DUPDATETIME=getdate() FROM ZDY_INTER_XIETONG a WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx );
                    DbHelper2.ExecuteNonQuery(sql, cConStr);                 
               
                return "ok";

            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                                    (ctarcode, [DUPDATETIME]
                          ,[CREMARK]
                          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;

            }


        }
        #endregion

        #region  插入考勤数据用到的类
        public class QueryTime
        {
            /// <summary>
            /// 查询开始时间
            /// </summary>
            public string starttime { get; set; }
            /// <summary>
            /// 查询结束时间
            /// </summary>
            public string endtime { get; set; }
        }


        public class ItemsItem
        {
            /// <summary>
            /// 
            /// </summary>
            public int id { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string pin { get; set; }
            /// <summary>
            /// 曹耀元
            /// </summary>
            public string ename { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string deptnumber { get; set; }
            /// <summary>
            /// 总部门
            /// </summary>
            public string deptname { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string checktime { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string sn { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string alias { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int verify { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string stateno { get; set; }
            /// <summary>
            /// 上班签到
            /// </summary>
            public string state { get; set; }
        }

        public class Data
        {
            /// <summary>
            /// 
            /// </summary>
            public int count { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<ItemsItem> items { get; set; }
        }

        public class KaoQinFanHui
        {
            /// <summary>
            /// 
            /// </summary>
            public int ret { get; set; }
            /// <summary>
            /// 获取考勤记录 776 条。
            /// </summary>
            public string msg { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public Data data { get; set; }
        }
        #endregion

        /// <summary>
        /// 目标账套是否有时间戳表
        /// </summary>
        /// <returns></returns>
        void CheckTimeTable()
        {
            string sql = @"IF EXISTS(SELECT name FROM [sysobjects] WHERE name = 'zdy_zd_stamp' and  xtype='U')
select 1
else select 0";
            int ire  =DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql,cConStrB));
            if (ire == 0)
            {
                sql = @"
CREATE TABLE [dbo].[zdy_zd_stamp](
	[id2] [INT] IDENTITY(1,1) NOT NULL,
	[clx] [VARCHAR](50) NULL,
	[cpk] [VARCHAR](50) NULL,
	[cpk2] [VARCHAR](50) NULL,
	[cpk3] [VARCHAR](50) NULL,
	[cpk4] [VARCHAR](50) NULL,
	[cpk5] [VARCHAR](50) NULL,
	[ctimestamp] [VARCHAR](100) NULL,
	[ddate2] [DATETIME] NULL,
 CONSTRAINT [PK_zdy_zd_stamp] PRIMARY KEY CLUSTERED 
(
	[id2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[zdy_zd_stamp] ADD  CONSTRAINT [DF_zdy_zd_stamp_ddate]  DEFAULT (getdate()) FOR [ddate]

";
                DbHelper2.ExecuteNonQuery(sql, cConStrB);
            }
        }

        #region 是否是yijia'检查状态
        bool checkYiJia()
        {
            String SQL = "SELECT VALUE FROM PARA WHERE MC='U8'";
            String cValue =DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStrZD));
            if (cValue == "1")
            {
                return true;
            
            }
            else
            {
                return false;
            }
            
        }
        #endregion

        #region  插入业务单据
        /// <summary>
        ///业务单据同步
        /// </summary>
        public string INSVouch666(string cLx, string cFilterOri)
        {
            try
            {
                //CheckTimeTable();
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[ctables],[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cfrom
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTables = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //取目标表数据的过滤
                    string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //取日期进行查询条件组合
                    //DateTime  dBeginDate  ;
                    sql = string.Format("  SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);

                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                      
                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                    //主键的处理
                    string cRelation = "";
                    string cPKS = "";  //主键的组合
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                        cPKS += string.Format("Tmp.{0},", i);
                    }
                    cPKS = cPKS.Substring(0, cPKS.Length - 1);
                    //时间戳的处理
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cStampRelation = "";
                    string[] newPKAarray = new string[5];
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //不足5位主键的补足5位，后面的补空‘’
                        for (int i = 0; i < 5; i++)
                        {
                            if (i < cPKArray.Length)
                            {
                                newPKAarray[i] = cPKArray[i];
                                if (i == 0)
                                {
                                    cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                }
                                else
                                {
                                    cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                }
                            }
                            else
                            {
                                newPKAarray[i] = "0";
                            }
                        }
                    }


                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cname from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //查询的字段，如果时间戳不为空，增加时间戳
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                    }
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //取目标表的id
                    //创建源库临时表，存放目标表的id
                    var srcTempTable = @"#TempSrc" + cTarTable;
                    var srcCreatetempsql = string.Format("select {0},T.ctimestamp into {1} from {2} Tmp left join zdy_zd_stamp T ON T.clx='{3}' where 1=2  ", cPKS, srcTempTable, cTarTable, cLx);
                    //获取取目标表的id
                    var srcselectsql = string.Format(@"select {0},T.ctimestamp from {1} Tmp left join zdy_zd_stamp T ON T.clx='{2}' {3}   where 1=1 {4} {5}",
                        cPKS, cTarTable, cLx, cStampRelation, cFilter, cFilterSrc);
                    DataTable srcdt = DbHelper2.ExecuteTable(srcselectsql, cConStrB);
                    //存储
                    DataTable dtIns = new DataTable();
                    DataTable dtDel = new DataTable();
                    DataTable dtUps = new DataTable();
                 

                    //查询源表和目标表的差异，insert表， delete 表和更新表

                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = srcCreatetempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = srcTempTable;
                                    bulkcopy.WriteToServer(srcdt);
                                    bulkcopy.Close();
                                }

                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  " +
                        "where not exists(select 1 from {2} Tmp where 1=1 {3}) {4}", cSelectColumn, cTarTable, srcTempTable, cRelation, cFilter);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];

                                var srcDelectsql = string.Format("select {0} from  {1} Tmp  " +
                        "where not exists(select 1 from {2} T where 1=1 {3} {4}) ", cPKS, srcTempTable, cTarTable, cRelation, cFilter);
                                command.CommandText = srcDelectsql;
                                adp = new SqlDataAdapter(command);
                                ds = new DataSet();
                                adp.Fill(ds);
                                dtDel = ds.Tables[0];

                                //单据更新状态下：有时间戳，才进行更新。
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    var srcUpdatesql = string.Format("select {0} from  {1} T  " +
                        "where exists(select 1 from {2} Tmp where convert(bigint,T.{4})<>Tmp.ctimestamp {3} ) ", cSelectColumn, cTarTable, srcTempTable, cRelation, cTimestamp);
                                    command.CommandText = srcUpdatesql;
                                    adp = new SqlDataAdapter(command);
                                    ds = new DataSet();
                                    adp.Fill(ds);
                                    dtUps = ds.Tables[0];

                                    
                                }

                            }
                            catch (Exception ex)
                            {
                                throw new Exception(string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableIns, cTarTable);

                    var tempTableDel = @"#TempDel" + cTarTable;
                    var createtempsqlDel = string.Format("select {0} into {1}  from {2} Tmp where 1=2", cPKS, tempTableDel, cTarTable);

                    //插入目标表数据
                    var insertsql = string.Format(@"insert into {0}({1}) select {4} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableIns, cRelation,cInsertValue);

                    //如果是条码的，增加 标识插入
                    if (cLx.Contains("MES"))
                    {
                        insertsql = string.Format("SET IDENTITY_Insert {0} ON ", cTarTable) + insertsql + string.Format(" SET IDENTITY_Insert {0} OFF ", cTarTable);
                    }
                    //更新的语句
                    var tempTableUps = @"#TempUps" + cTarTable;
                    var createtempsqlUps = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableUps, cTarTable);
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableUps, cRelation);
     
                    #region 单据替换
                    //// 仓库 替换
                    //var updatewhIns = "";
                    //var updatewhUps = "";
                    //if (cInsertColumn.ToLower().Contains("cwhcode,"))
                    //{
                    //    updatewhIns = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableIns);
                    //    updatewhUps = string.Format(" update a set cwhcode = b.cwhcode2 from {0} a,zdy_zd_yj_whduizhao b where a.cwhcode = b.cwhcode", tempTableUps);
                    //}
                    ////供应商 替换
                    //var updatevenIns = "";
                    //var updatevenUps = "";
                    //if (cInsertColumn.ToLower().Contains("cvencode,"))
                    //{
                    //    updatevenIns = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableIns);
                    //    updatevenUps = string.Format(" update a set cvencode = b.cvencode2 from {0} a,zdy_zd_yj_venduizhao b where a.cvencode = b.cvencode", tempTableUps);
                    //}
                    ////存货 替换
                    //var updateinvIns = "";
                    //var updateinvUps = "";
                    //if (cInsertColumn.ToLower().Contains("cinvcode,"))
                    //{
                    //    updateinvIns = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableIns);
                    //    updateinvUps = string.Format(" update a set cinvcode = b.cinvcode2 from {0} a,zdy_zd_yj_invduizhao b where a.cinvcode = b.cinvcode", tempTableUps);
                    //}
                  
                   
                    var deletesql = "";
                    deletesql += string.Format(" delete From T from {0} T  where exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableDel, cRelation, cDelFilter);

                    var dropsql = string.Format("DROP TABLE {0}  DROP TABLE {1} DROP TABLE {2} ;", tempTableIns, tempTableDel, tempTableUps);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                    #endregion

                    #region 人员考勤替换
                  
                    //考勤时间处理
                    sql = string.Format(@"  SELECT STUFF((select  ','+ccolumn+'='+cdangan from zdy_inter_tables WHERE clx = '{0}'
 and bmust=1 and isnull(cdangan,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    var cUpdateColunmTime = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    var insertsqlTime = string.Format("UPDATE {1} SET {0} FROM {1}   ", cUpdateColunmTime, tempTableIns);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdangan from zdy_inter_tables WHERE clx = '{0}'
 and bmust=1 and isnull(cdangan,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    var cUpdateColunmTime2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    var updatesqlTime = string.Format("UPDATE {1} SET {0} FROM {1}   ", cUpdateColunmTime2, tempTableUps);
                    #endregion
                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                //没有时间戳不更新
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    command.CommandText = createtempsqlUps;
                                    command.ExecuteNonQuery();
                                    //更新
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableUps;
                                        bulkcopy.WriteToServer(dtUps);
                                        bulkcopy.Close();
                                    }

                                   
                                

                                    if (!string.IsNullOrEmpty(cUpdateColunmTime2))
                                    {
                                        command.CommandText = updatesqlTime;
                                        command.ExecuteNonQuery();
                                    }

                                    //执行更新,有更新栏目的才进行更新
                                    command.CommandTimeout = 300;
                                    if (!string.IsNullOrEmpty(cUpdateColunm))
                                    {
                                        command.CommandText = updatesql;
                                        updateQuery = command.ExecuteNonQuery();
                                    }

                                }

                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }
                              
                                if (!string.IsNullOrEmpty(cUpdateColunmTime))
                                {
                                    command.CommandText = insertsqlTime;
                                    command.ExecuteNonQuery();
                                }
                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();


                                command.CommandText = createtempsqlDel;
                                command.ExecuteNonQuery();
                                //删除
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableDel;
                                    bulkcopy.WriteToServer(dtDel);
                                    bulkcopy.Close();
                                }

                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();

                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                  select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                  cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableIns, cStampRelation);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableUps, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    //删除
                                    command.CommandText = string.Format(@" delete  from  T from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} ", cTimestamp, tempTableDel, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region  插入业务单据，新增的
        /// <summary>
        ///业务单据同步
        /// </summary>
        public string INSVouchAdd(string cLx, string cFilterOri)
        {
            try
            {
                //CheckTimeTable();
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[ctables],[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cfrom
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTables = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //取目标表数据的过滤
                    string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //取日期进行查询条件组合
                    //DateTime  dBeginDate  ;
                    sql = string.Format("  SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = {0} AND clx = '{1}'  ", iDBID, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);

                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }

                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                    //主键的处理
                    string cRelation = "";
                    string cPKS = "";  //主键的组合
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                        cPKS += string.Format("Tmp.{0},", i);
                    }
                    cPKS = cPKS.Substring(0, cPKS.Length - 1);
                    //时间戳的处理
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cStampRelation = "";
                    string[] newPKAarray = new string[5];
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //不足5位主键的补足5位，后面的补空‘’
                        for (int i = 0; i < 5; i++)
                        {
                            if (i < cPKArray.Length)
                            {
                                newPKAarray[i] = cPKArray[i];
                                if (i == 0)
                                {
                                    cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                }
                                else
                                {
                                    cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                }
                            }
                            else
                            {
                                newPKAarray[i] = "0";
                            }
                        }
                    }


                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cname from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //查询的字段，如果时间戳不为空，增加时间戳
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                    }
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //取目标表的id
                    //创建源库临时表，存放目标表的id
                    var srcTempTable = @"#TempSrc" + cTarTable;
                    var srcCreatetempsql = string.Format("select {0},T.ctimestamp into {1} from {2} Tmp left join zdy_zd_stamp T ON T.clx='{3}' where 1=2  ", cPKS, srcTempTable, cTarTable, cLx);
                    //获取取目标表的id
                    var srcselectsql = string.Format(@"select {0},T.ctimestamp from {1} Tmp left join zdy_zd_stamp T ON T.clx='{2}' {3}   where 1=1 {4} {5}",
                        cPKS, cTarTable, cLx, cStampRelation, cFilter, cFilterSrc);
                    DataTable srcdt = DbHelper2.ExecuteTable(srcselectsql, cConStrB);
                    //存储
                    DataTable dtIns = new DataTable();
                    DataTable dtDel = new DataTable();
                    DataTable dtUps = new DataTable();


                    //查询源表和目标表的差异，insert表， delete 表和更新表

                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = srcCreatetempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = srcTempTable;
                                    bulkcopy.WriteToServer(srcdt);
                                    bulkcopy.Close();
                                }

                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  " +
                        "where not exists(select 1 from {2} Tmp where 1=1 {3}) {4}", cSelectColumn, cTarTable, srcTempTable, cRelation, cFilter);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];

                            }
                            catch (Exception ex)
                            {
                                throw new Exception(string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableIns, cTarTable);

                 
                    //插入目标表数据
                    var insertsql = string.Format(@"insert into {0}({1}) select {4} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableIns, cRelation, cInsertValue);

                    //如果是条码的，增加 标识插入
                    if (cLx.Contains("MES"))
                    {
                        insertsql = string.Format("SET IDENTITY_Insert {0} ON ", cTarTable) + insertsql + string.Format(" SET IDENTITY_Insert {0} OFF ", cTarTable);
                    }
                    //更新的语句
                    var tempTableUps = @"#TempUps" + cTarTable;
                    var createtempsqlUps = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableUps, cTarTable);
                   
         
             
                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                //没有时间戳不更新
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    command.CommandText = createtempsqlUps;
                                    command.ExecuteNonQuery();
                                    //更新
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableUps;
                                        bulkcopy.WriteToServer(dtUps);
                                        bulkcopy.Close();
                                    }

                                   

                                }

                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }

                                
                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();


                              
                               

                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                  select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                  cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableIns, cStampRelation);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableUps, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                   

                                }
                                cError = string.Format("新增{0}条数据", insertQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion
    }
}
